package com.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class JDBC {
//    public static String driver = "com.mysql.cj.jdbc.Driver";
//    //数据库
//    public static String database="jsf";
//    //链接路径
//    public static String url = "jdbc:mysql://192.168.1.39:3306/"+database+"?serverTimezone=Asia/Shanghai&&useSSL=false";
//    public static String user = "lijiachen";
//    public static String pass = "root";
    //1.注册驱动
    private static DataSource dataSource;
    static {
        Properties properties=new Properties();
        InputStream is=JDBC.class.getResourceAsStream("db.properties");
        try {
            properties.load(is);
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
//        try {
//            Context context=new InitialContext();
//            dataSource=(DataSource) context.lookup("java:comp/env/jdbc/daily");
//        } catch (NamingException e) {
//            e.printStackTrace();
//        }
//        try {
//            Class.forName(driver);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
    }
    //2.获得连接对象
    public static Connection getConn(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //3、 设置sql语句中的参数
    public static void setParams(PreparedStatement ps,Object... obj){
        if(obj.length>0) {
            for (int i = 0; i < obj.length; i++) {
                try {
                    ps.setObject(i+1,obj[i]);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
    //4、执行sql语句  insert返回  主键int值
    public static int sqlInsert(String sql,Object... obj){//传入sql语句
        Connection con=null;
        PreparedStatement ps =null;
        ResultSet set = null;
        try {
            con = getConn();
            ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            setParams(ps,obj);
            ps.executeUpdate();
            set = ps.getGeneratedKeys();
            if(set.next()){
                return set.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(set,ps,con);
        }
        return -1;
    }
    //4、执行sql语句 update insert delete  返回int 数据库中影响行数
    public static int sqlUpdate(String sql,Object... obj){//传入sql语句
        Connection con=null;
        PreparedStatement ps =null;
        try {
            con = getConn();
            ps =con.prepareStatement(sql);
            setParams(ps,obj);
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(null,ps,con);
        }
        return -1;
    }
    //4.执行sql语句 select 返回arraylist map集合 中间包含字段名
    public static List<Map> selectMap(String sql, Object... objects){
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet set = null;
        List<Map> tlist = new ArrayList();
        try {
            c = getConn();
            ps = c.prepareStatement(sql);
            setParams(ps,objects);
            set=ps.executeQuery();
            ResultSetMetaData metaData = set.getMetaData();
            int count = metaData.getColumnCount();
            while(set.next()){
                Map map = new LinkedHashMap();
                for (int i = 1; i <= count; i++) {
                    map.put(metaData.getColumnLabel(i),set.getString(i));
                }
                tlist.add(map);
            }
            return tlist;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(set,ps,c);
        }
        return null;
    }
    //4.执行sql语句 select 返回arraylist集合  中间不包含字段名
    public static List<List> selectArray(String sql, Object... objects){
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet set = null;
        List<List> tlist = new ArrayList<>();
        try {
            c = getConn();
            ps = c.prepareStatement(sql);
            setParams(ps,objects);
            set=ps.executeQuery();
            ResultSetMetaData metaData = set.getMetaData();
            int count = metaData.getColumnCount();
            while(set.next()){
                ArrayList rlist = new ArrayList();
                for (int i = 1; i <= count; i++) {
                    rlist.add(set.getObject(i));
                }
                tlist.add(rlist);
            }
            return tlist;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(set,ps,c);
        }
        return null;
    }
    //5、关闭数据库连接
    public static void close(ResultSet set, Statement sta, Connection con){
        if(set!=null){
            try {
                set.close();
                set=null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(sta!=null){
            try {
                sta.close();
                sta=null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
                con=null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
